/*TODO: 
 * add numberOfTimes book has been checked out to add books, added to DB
 * add time checked out to messages
 * make requestBookID -1 override class
 * return UTC time from SQLite
 * 
 * searches, general = all, from (LIMIT) (start, end)
 * return counts
 * return topCheckedOut books
 * search: title, isbn, author, status <-needed class's for resquest has it all
 * omni
 * 
 * add the make an addUser function
 * make the messagesDB
 * make a function get retrieve messages (class for messages required first
 * make a function to add messages
 * start planning the get(s) for the three DB's(can return 0,1 or multiple results, think of best way to handle)
 * also the get(s) will have not always have a strict input, might have things like search results
 * either read from config file or make the constructor retrieve what type of DB we are using
 * decide how to handle 2 db's, break this into 3 class's, on for sqlite, one for mysql and then this class which call the function for those 2
 * 
 * find out if wrong db is selected if we should default to sqllite or error out, right now it errors out
 * */
//make usernames unique
package org.libarae.server;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import javax.imageio.ImageIO;


import org.libarae.communication.common.Book;
import org.libarae.communication.common.Book.BookStatus;
import org.libarae.communication.common.Message;
import org.libarae.communication.common.MessageThread;
import org.libarae.communication.common.User;
import org.libarae.communication.common.User.Permission;
import org.libarae.server.databases.SQLite;
import org.libarae.server.databases.mySQL;

public class Database {
	private int dbType;//this is the type of db, 0=SQLite, 1=mySQL...
	private SQLite SQLiteDB;
	private mySQL mySQLDB;
	//testing methods, just for testing, delete before build

	
	//tested AND WORK

	public Database(int db) {
		if(db==0) {
			dbType=0;
			SQLiteDB = SQLite.getInstance(); 
		}else if(db==1){
			dbType =1;
			mySQLDB = mySQL.getInstance();
		}
		else
		{
			System.out.println("Fatal Error, this is not a valid database type.");
			System.exit(-1);
		}
	}
	
	//this function drops all the tables from the database
	public void deleteDB() {
		SQLiteDB.deleteDB();
	}
	public void close() {
		if(dbType==0) {
			SQLiteDB.close();
		}else if(dbType==1){
			mySQLDB.close();
		}
		
	}
	public void open() {
		if(dbType==0) {
			SQLiteDB.open();
		}else if(dbType==1){
			mySQLDB.open();
		}
		
	}
	//this function check for current tables and adds any missing ones
	public void createDB() {
		try {
			if(dbType==0) {
				SQLiteDB.createDB();
			}
			else if(dbType==1) {
				mySQLDB.createDB();
			}
		} catch (Exception e) {
			System.out.println("Error creating database");
			if(Server.config.inDebugMode()) { //DEBUG MESSAGE
				System.out.println("Printing Exception:");
				e.printStackTrace();
			}
			System.out.println("Exiting...");
			System.exit(-1);
		}
	
	
		               
	}
	
	/*----------------------------------addUser----------------------------
	 * 
	 *add the user to the DB, you may either tell it the individual fields
	 * for give it an User class
	 * returns a boolean, true if teh suer was added to the db false if he 
	 * was not (userName already in use)
	 * 
	 */
	public User addUser(User u) throws SQLException {
		return this.addUser(u.getFirstName(), u.getLastName(), u.getUserName(), u.getPassword(), u.getStatus(), u.getAddress(), u.getCity(), u.getState(), u.getZipCode());
	}
	public User addUser(String first, String last, String userName, String password, Permission userLevel, String address, String city, String state, String zip) throws  SQLException {		
		String sql = "INSERT INTO members (firstName, lastName, userName, password, userLevel, address, city, state, zip) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";
		if(dbType==0) {
			if(SQLiteDB.addUser(sql, first, last, userName, password, userLevel, address, city, state, zip)) {
				User u = this.userLogin(userName, password);
				return u;
			}
		}else if(dbType==1) {
			if(mySQLDB.addUser(sql, first, last, userName, password, userLevel, address, city, state, zip)) {
				User u = this.userLogin(userName, password);
				return u;
			}
		}
		return null;
	}
	
	/*----------------------------addBook---------------------------
	 * adds a book tot eh Database based on eitehr a Book object
	 * or on giving it certain values, function has 2 overides
	 * Returns true if the book was added, flase if it was not
	 */
	public boolean addBook(int ownerID, String title, String isbn, String author, String publicationDate, String summary, String publisher, String picture) {
		
		String sql = "INSERT INTO books (ownerID, title, isbn, author, pubDate, addedDate, summary, publisher, bookStatus, picture, checkOutCount, checkedOutBy) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		if(dbType==0) {
			return SQLiteDB.addBook(sql, ownerID, title, isbn, author, publicationDate, summary, publisher, BookStatus.AVAILABLE, picture, this.getDate());
		} else if(dbType==1) {
			return mySQLDB.addBook(sql, ownerID, title, isbn, author, publicationDate, summary, publisher, BookStatus.AVAILABLE, picture, this.getDate());
		}
		this.close();
		return false;
		
	}
	//overloads for the previous addBook statement
	public boolean addBook(Book b) {	
			return this.addBook(b.getOwnerID(), b.getTitle(), b.getISBN(),b.getAuthor(), b.getPubDate(), b.getSummary(), b.getPublisher(), savePicToServer(b.getPicture(), b.getISBN(), false));
	}	
	public boolean addBook(int ownerID, String title, String isbn, String author, String publicationDate, String summary, String publisher) {
		return this.addBook(ownerID, title, isbn, author, publicationDate, summary, publisher, "none");
	}
	
	
	/*----------------------- User Login --------------------
	 * This function take a userName and password and checks it against
	 * those in the database, if it is a match it will return the information
	 * pertaining to that user
	 */
	public User userLogin(String user, String password) throws SQLException {
		String sql = "SELECT * FROM members WHERE userName=? and password=?;";
		if(dbType==0) {
			return SQLiteDB.userLogin(sql, user, password);		
		}else if(dbType==1) {
			return mySQLDB.userLogin(sql, user, password);	
		}
		
		return null;
	}
	
	/*----------------------Server Table ---------------
	 * These functions retrieve information about server related settings
	 * The Functions are:
	 * 1. set/get ServerPassword : This function will get or retrieve a password for the server
	 * 2. set/get ServerMessage : This function will get or set a server message
	 * 3. set/get ISBNDBkey : This function is you you set or retrieve the isbnDB key, 
	 *			this key is required to use the external database. Since the key is free
	 *			it is strong recommended to have on for this service.
	 */
	public boolean setServerPassword(String password) {
		String sql = "UPDATE serverInformation SET password=? WHERE sID=1;";
		if(dbType==0){
			return SQLiteDB.setServerInfo(sql, password);
		}else if(dbType==1) {
			return mySQLDB.setServerInfo(sql, password);
		}
		return false;
		
	}
	public boolean setServerMessage(String message) {
		String sql = "UPDATE serverInformation SET message=? WHERE sID=1;";
		if(dbType==0) {
			return SQLiteDB.setServerInfo(sql, message);
		} else if(dbType==1) {
			return mySQLDB.setServerInfo(sql, message);
		}
		return false;
		
	}
	public boolean setISBNDBkey(String key) {
		String sql = "UPDATE serverInformation SET isbndbKey=? WHERE sID=1;";
		if(dbType==0) {
			return SQLiteDB.setServerInfo(sql, key);
		}else if(dbType==1) {
			return mySQLDB.setServerInfo(sql, key);
		}
		return false;
	}
	public String getISBNDBkey() {
		String sql = "SELECT isbndbKey FROM serverInformation WHERE sID=1;";
		if(dbType==0) {
			return SQLiteDB.getGeneralServerInfo(sql);
		}else if(dbType==1) {
			return mySQLDB.getGeneralServerInfo(sql);
		}
		return null;
	}
	public String getServerMessage() {
		String sql = "SELECT message FROM serverInformation WHERE sID=1;";
		if(dbType==0) {
			return SQLiteDB.getGeneralServerInfo(sql);
		}else if(dbType==1) {
			return mySQLDB.getGeneralServerInfo(sql);
		}
		return null;
	}
	public String getServerPassword() {
		String sql = "SELECT password FROM serverInformation WHERE sID=1;";
		if(dbType==0) {
			return SQLiteDB.getGeneralServerInfo(sql);
		}else if(dbType==1) {
			return mySQLDB.getGeneralServerInfo(sql);
		}
		return null;
	}
	
	/*-------------------delete funcation-------------
	 * These are general functions for delete user and
	 * delete books. The delete user function deltes
	 * all teh books that user owns and his account
	 * while the delte book function deletes a single book
	 * both funcation return a true or flase to tell
	 * if the item was sucessfully deleted.
	 */
	
	public boolean deleteUser(int userID) {
		String sql = "DELETE FROM books WHERE ownerID="+userID;
		boolean books = SQLiteDB.generalDelete(sql);
		sql = "DELETE FROM members WHERE memberID="+userID;
		return (SQLiteDB.generalDelete(sql) && books);
	}
	public boolean deleteUser(User u) {
		return deleteUser(u.getUID());
	}
	public boolean deleteBook(int bookID) {
		String sql = "DELETE FROM books WHERE bookID="+bookID;
		return SQLiteDB.generalDelete(sql);
	}
	public boolean deleteBook(Book b) {
		return deleteBook(b.getBID());
	}
	

	/*------------------search--------------
	 * This is a search fucntion to search through all the books
	 * in the DB. It takes specific fields or an omni search, or
	 * any combination of all of them to search throught he fields.
	 * The omni search allows you to search for a keyword through multiple
	 * fields in case you are not sure what field it was contained in
	 * It returns and ArrayList of books.
	 */
	

	public ArrayList<Book> search(String title, String isbn, String author, String summary, String publisher, String omni, BookStatus status, String pubDate, int start, int end){
		String sql = "SELECT * FROM books WHERE ";
 		String sqlAnd = "";
 		String sqlOr = "";
 		if(!title.equals("")) {
 			sqlAnd +=("title LIKE '%"+title+"%' and ");
 		}
 		else if(!omni.equals("")) {
 			sqlOr +=("title LIKE '%"+omni+"%' or ");
 		}
 		
 		if(!isbn.equals("")) {
 			sqlAnd +=("isbn LIKE '%"+isbn+"%' and ");
 		}
 		else if(!omni.equals("")) {
 			sqlOr +=("isbn LIKE '%"+omni+"%' or ");
 		}
 		
 		if(!author.equals("")) {
 			sqlAnd +=("author LIKE '%"+author+"%' and ");
 		}
 		else if(!omni.equals("")) {
 			sqlOr +=("author LIKE '%"+omni+"%' or ");
 		}
 		
 		if(!summary.equals("")) {
 			sqlAnd +=("summary LIKE '%"+summary+"%' and ");
 		}
 		else if(!omni.equals("")) {
 			sqlOr +=("summary LIKE '%"+omni+"%' or ");
 		}
 		
 		if(!publisher.equals("")) {
 			sqlAnd +=("publisher LIKE '%"+publisher+"%' and ");
 		}
 		else if(!omni.equals("")) {
 			sqlOr +=("publisher LIKE '%"+omni+"%' or ");
 		}
 		if(!pubDate.equals("")) {
 			sqlAnd +=("pubDate LIKE '%"+pubDate+"%' and");
 		} 
 		else if(!omni.equals("")) {
 			sqlOr +=("pubDate LIKE '%"+omni+"%' or ");
 		}
 		if(status!=null) {
 			sqlAnd +=("bookStatus="+status.ordinal()+" and");
 		} 

 		
 		if(sqlAnd.length()<1 && sqlOr.length()<1)
 			sql = "SELECT * FROM books";
 		

 		sql +=sqlAnd;
 		
 		if(sqlOr.length()>1){
 			sqlOr = sqlOr.substring(0, sqlOr.length()-4);
 			sql +=("("+sqlOr+")");
 		}
 		
 		if(sqlAnd.length() >1 && sqlOr.length()<1)
 			sql = sql.substring(0, sql.length()-4);
 		int count = end-start;
 		sql = "SELECT * FROM ("+sql+") ORDER BY title";
 		sql +=" LIMIT "+start+", " + count +";";
 		if(Server.config.inDebugMode()) //DEBUG MESSAGE
 			System.out.println(sql);
 		return SQLiteDB.generalBooksSQL(sql);

 	}
	
	
	/*-----------------return all*----------
	 * These functions return all the users or books in the database
	 * They return all users sorted by userName and all books
	 * sorted by title
	 * return Type: arrayList
	 */
	public ArrayList<User> getAllUsers(int start, int end) {
		int count =end - start;
		String sql = "SELECT * FROM members ORDER BY userName LIMIT "+start+","+count+";";
		return SQLiteDB.generalUserSQL(sql);
	}
	public ArrayList<Book> getAllBooks(int start, int end) {
		int count =end - start;
		String sql = "SELECT * FROM books ORDER BY title LIMIT "+start+","+count+";";
		return SQLiteDB.generalBooksSQL(sql);
	}
	
	/*---------------get books owned by---------------
	 * This funcation takes a user'd ID and returns all the books
	 * that he currently ahs in the system, weither currently with him
	 * or checkd out by another user
	 */
 	public ArrayList<Book> getBooksOwnedBy(int ownerID) {
 		String sql = "SELECT * FROM books WHERE ownerID="+ownerID;
 		return SQLiteDB.generalBooksSQL(sql);

 	}
	
 	/*----------------get User and Book ---------------
 	 * These are the functions get users and books. Once you 
 	 * send them the required data (usually an ID) but some can take
 	 * a userName or other similar data it will return a Book or User class
 	 */	
 	public User getUser(int userID) {
 		String sql = "SELECT * FROM members WHERE memberID=" + userID;
		if(dbType==0) {
			return SQLiteDB.getUser(sql);
		}
		return null;
	}
	public Book getBook(int bookID){
		String sql  = "SELECT * FROM books WHERE bookID="+bookID;
		if(dbType==0) {
			return SQLiteDB.getBook(sql);	
		}
		return null;
	}
	public User getUser(String userName) {
		String sql = "SELECT * FROM members WHERE userName=?";
		if(dbType==0) {
			return SQLiteDB.getUser(sql, userName);
		}
		return null;
	}

	/*-----------------updateBookStatus----------------------
	 * This funcation updates a book status given teh value in the Book
	 * class or given teh book id, status, and who checked it out (or -1
	 * if not being checked out). It then returns a boolean to
	 * confirm in the update took place.
	 */
	public boolean updateBookStatus(int bookID, BookStatus status, int checkedOutBy){ //-1 on checkedOutBY means the owner has the book, it is checked Out By no one
		String sql = "UPDATE books SET bookStatus="+status.ordinal()+", checkedOutBy="+checkedOutBy+" WHERE bookID="+bookID+";";
		String sql2 = "UPDATE books SET checkOutCount = checkOutCount + 1 WHERE bookID="+bookID+";";
		if(dbType==0) {
			if(status == BookStatus.CHECKED_OUT)
				return SQLiteDB.updateBookStatus(sql, sql2);
			else
				return SQLiteDB.generalUpdate(sql);
		}
		return false;
	}
	public boolean updateBookStatus(Book b) {
		return updateBookStatus(b.getBID(), b.getBookStatus(), b.getCheckedOutBy());
	}
	/*----------------getBooksWith*---------------------
	 * These funcations return a set number of books with the
	 * most checkouts or the most recently added to the database.
	 * It returns an arrayList of books
	 * 
	 */
	public ArrayList<Book> getBooksWithMostCheckout(int start, int end) {
		int count = end-start;
		String sql = "SELECT * FROM books ORDER BY checkOutCount DESC LIMIT "+start+","+count+";";
		return SQLiteDB.generalBooksSQL(sql);
	}
	public ArrayList<Book> getBooksWithMostCheckout(int numberToGet) {
		return getBooksWithMostCheckout(0, numberToGet);
	}
	public ArrayList<Book> getMostRecentBooks(int start, int end) {
		int count = end-start;
		String sql = "SELECT * FROM books ORDER BY addedDate DESC LIMIT "+start+","+count+";";
		return SQLiteDB.generalBooksSQL(sql);
	}
	public ArrayList<Book> getMostRecentBooks(int numberToGet) {
		return getMostRecentBooks(0, numberToGet);
	}
	
	/*-------------update*-----------------
	 * These funcation update teh book and User, but they WILL not update
	 * the book status, you will need to call updateBookStatus for that
	 * These funcation will update a user level if needed though.
	 */
	public boolean updateBook(Book b) {
		String sql = "UPDATE books SET title=?, isbn=?, author=?, pubDate=?, summary=?, publisher=?, picture=? WHERE bookID="+b.getBID()+";";
		return SQLiteDB.updateBook(sql, b, this.savePicToServer(b.getPicture(), b.getISBN(), true));
	}
	public boolean updateUser(User u) {
		String sql = "UPDATE members SET firstName=?, lastName=?, userLevel=?, address=?, city=?, state=?, zip=? WHERE memberID="+u.getUID()+";";
		return SQLiteDB.updateUser(sql, u);
	}
	/*-----------getBooksLoaning/Loaned------------
	 * These function will return books currently checked out by other people
	 * the get books loaning function tells you what books the user with the sent USERID
	 * currently has checked out from other users. The get books loaned out 
	 * currently tells people what book(s) the user with the sent USERID
	 * has lent out to other people.
	 */
	public ArrayList<Book> getBooksLoaning(int myUserID, int start, int end){
		int count = end-start;
		String sql = "SELECT * FROM books WHERE checkedOutBy="+myUserID+" LIMIT "+start+","+count+";";
		return SQLiteDB.generalBooksSQL(sql);
	}
	public ArrayList<Book> getBooksLoanedOut(int myUserID, int start, int end) {
		int count = end-start;
		String sql = "SELECT * FROM books WHERE ownerID="+myUserID+" and NOT checkedOutBy=-1 LIMIT "+start+","+count+";";
		return SQLiteDB.generalBooksSQL(sql);	
	}
	
	//not tested
	
	public boolean addMessageToThread(Message m) {
		long time = getDate();
		String sql="INSERT INTO messages (message, toUser, threadID, isRead, messageTime, isPending) VALUES (?, ?, ?, ?, ?, ?);";
		if(Server.config.inDebugMode()) //DEBUG MESSAGE
			System.out.println("need approval?: "+ m.isNeedsApproval());
		if(SQLiteDB.addMessageToThread(sql, m.getMessageText(), m.getToUser(), m.getThreadId(), m.isIsRead(), time, m.isNeedsApproval())) {
			sql = "UPDATE threads SET time="+time+" WHERE threadID="+m.getThreadId()+";";
			return SQLiteDB.generalMessageUpdate(sql);
		}
		return false;
		
	}
	public boolean setIsPending(int mid, boolean pending) {
		int myPend = 0;
		if(pending) 
			myPend =1;
		String sql = "UPDATE messages SET isPending="+myPend+";";
		if(dbType==0) {
			return SQLiteDB.generalUpdate(sql);
		}else if(dbType==1) {
			return mySQLDB.generalUpdate(sql);
		}
		return false;
	}
	public int getUnreadMessageCount(int userID){
		String sql = "SELECT COUNT(messageID) FROM messages WHERE toUser="+userID+" and isRead=0";
		return SQLiteDB.generalCount(sql);
	}
	public boolean newMessage(MessageThread t, Message m) {
 		String sql = "INSERT INTO threads (userID, userID2, subject, bookID) VALUES (?, ?, ?, ?);";
		if( SQLiteDB.addThread(sql, t.getUser1(), t.getUser2(), t.getSubject(), t.getBid())) {
			sql = "SELECT max(threadID) FROM threads;";
			int threadID = SQLiteDB.generalCount(sql);
			m.setThreadId(threadID);
			return addMessageToThread(m);
		}
		
		return false;
	}
	public int getFirstMessageInThread(int threadID){
		String sql = "SELECT messageID FROM messages WHERE messageTime=(SELECT MIN(messageTime) FROM messages WHERE threadID=1);";
		int b= SQLiteDB.generalCount(sql);
		if(Server.config.inDebugMode()) //DEBUG MESSAGE
			System.out.println("MessageID: "+ b);
		return b;
	}
	public boolean markRead(int messageID, boolean isread) {
		int read =0;
		if(isread) 
			read=1;
		String sql = "UPDATE messages SET isRead="+read+" WHERE messageID="+messageID+";";
		return SQLiteDB.generalUpdate(sql);
		
		
	}
	public ArrayList<MessageThread> getThreads(int userID) {
		ArrayList<MessageThread> mt;
		String sql = "SELECT * FROM (SELECT * FROM threads WHERE userID="+userID+" or userID2="+userID+") ORDER BY time;";
		mt = SQLiteDB.getThreads(sql);
		for(int i=0;i<mt.size();i++) {
			int unreadInThread = getUnreadMessageCount(mt.get(i).getUser1());
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("has unread messages#1: " +unreadInThread);
			unreadInThread += getUnreadMessageCount(mt.get(i).getUser2());
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("has unread messages#2: " +unreadInThread);
			boolean hasUnRead = false;
			if (unreadInThread>0)
				hasUnRead=true;
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("has unread message: " +hasUnRead);
			mt.get(i).setHasUnRead(hasUnRead);
			String username = this.getUser((mt.get(i).getUser1())).getUserName();
			mt.get(i).setUserName1(username);
			username = this.getUser((mt.get(i).getUser2())).getUserName();
			mt.get(i).setUserName2(username);
			
		}
		return mt;
		
		
		
	}
	public ArrayList<Message> getMessages (int threadID) {
		String sql = "SELECT * FROM (SELECT * FROM messages WHERE threadID="+threadID+") ORDER BY messageTime;";
		return SQLiteDB.getMessages(sql);
		
	}
	
 	//private class functions
	private long getDate(){
		Date now = new Date();
		long time =now.getTime();
		return time;
	}
	private String savePicToServer(BufferedImage bf, String isbn, boolean isUpdate) {
		File myFile = new File("images/"+isbn+".png");
		if(Server.config.inDebugMode())//DEBUG MESSAGE
			System.out.println("ISBN: "+isbn);
		if(myFile.exists() && isUpdate==false){
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("image exist already: "+myFile.getAbsolutePath());
			return myFile.getAbsolutePath();
			
		}
		else {
			if(bf !=null) {
				if(Server.config.inDebugMode())//DEBUG MESSAGE
					System.out.println("bf is not null");
			    try {
					ImageIO.write(bf, "png", myFile);
					if(Server.config.inDebugMode())//DEBUG MESSAGE
						System.out.println("image added at: "+myFile.getAbsolutePath());
					return myFile.getAbsolutePath();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					if(Server.config.inDebugMode())//DEBUG MESSAGE
						e.printStackTrace();
				}
			    
			}
			if(Server.config.inDebugMode())//DEBUG MESSAGE
				System.out.println("image did not exist and does not have iamge");
			return "";

		}
	}
		

}